package cn.edu.gzhmt.goods.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import cn.edu.gzhmt.category.dao.ICategoryDao;
import cn.edu.gzhmt.category.dao.impl.CategoryDaoImpl;
import cn.edu.gzhmt.category.domain.Category;
import cn.edu.gzhmt.goods.dao.IGoodsDao;
import cn.edu.gzhmt.goods.domain.Goods;
import cn.edu.gzhmt.goods.domain.SortGoods;
import cn.edu.gzhmt.utils.jdbc.TxQueryRunner;

/**
 * 商品dao层实现类
 * @author azhuo
 *
 */
public class GoodsDaoImpl implements IGoodsDao {

	private ICategoryDao categoryDao = new CategoryDaoImpl();

	private QueryRunner queryRunner = new TxQueryRunner();

	/**
	 * 查询所有商品
	 */
	public List<Goods> findAll() {
		try {
			String sql = "SELECT * FROM `t_goods` WHERE gdel=0";
			List<Goods> goodsList = queryRunner.query(sql,
					new BeanListHandler<Goods>(Goods.class));

			// 加载商品所属分类
			if (goodsList != null) {
				for (Goods goods : goodsList) {
					loadCategory(goods);
				}
			}

			return goodsList;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

	}

	/**
	 * 为商品加载分类
	 * @param goods
	 */
	private void loadCategory(Goods goods) {

		Category category = categoryDao.findById(goods.getCid());
		goods.setCategory(category);
	}

	/**
	 * 通过id查询商品
	 */
	public Goods findById(String gid) {
		try {
			String sql = "SELECT * FROM `t_goods` WHERE gid=?";

			Goods goods = queryRunner.query(sql, new BeanHandler<Goods>(
					Goods.class), gid);
			// 加载商品所属的分类
			if (goods != null) {
				loadCategory(goods);
			}
			return goods;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 按名称模糊查询
	 */
	public List<Goods> findByName(String gname) {
		try {
			String sql = "SELECT * FROM `t_goods` WHERE gname LIKE ?";
			gname = "%" + gname + "%";
			return queryRunner.query(sql, new BeanListHandler<Goods>(
					Goods.class), gname);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 通过 分类id 查询商品
	 */
	public List<Goods> findByCategoryId(String cid) {
		try {
			String sql = "SELECT * FROM `t_goods` WHERE cid=?  LIMIT 0,30";
			return queryRunner.query(sql, new BeanListHandler<Goods>(
					Goods.class), cid);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 通过 分类id 查询商品
	 */
	public List<Goods> findByCategoryIdLimit(String cid, int start, int pageSize) {
		try {
			String sql = "SELECT * FROM `t_goods` WHERE cid=?  LIMIT ?,?";
			Object[] params = { cid, start, pageSize };
			return queryRunner.query(sql, new BeanListHandler<Goods>(
					Goods.class), params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 通过gid删除商品
	 * 假删除
	 */
	public void removeByGid(String gid) {
		try {
			String sql = "UPDATE `t_goods` SET `gdel`=1 WHERE gid=?";

			queryRunner.update(sql, gid);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 多条件组合查询商品
	 * 这里只是用了：商品名称模糊查询、分类查询
	 */
	public List<Goods> findGoods(Goods form) {
		try {
			StringBuffer sqlBuf = new StringBuffer();
			sqlBuf.append("SELECT * FROM `t_goods` WHERE gdel=0");

			List<Object> params = new ArrayList<Object>();
			// 判断名称
			if (form.getGname() != null && form.getGname().trim().length() >= 0) {
				sqlBuf.append(" AND gname LIKE ?");
				params.add("%" + form.getGname() + "%");
			}

			// 判断分类
			if (form.getCategory() != null
					&& form.getCategory().getCid() != null
					&& form.getCategory().getCid().trim().length() >= 0) {
				sqlBuf.append(" AND cid=?");
				params.add(form.getCategory().getCid());
			}

			List<Goods> goodsList = queryRunner.query(sqlBuf.toString(),
					new BeanListHandler<Goods>(Goods.class), params.toArray());
			// 为图书进行管理分类
			for (Goods goods : goodsList) {
				loadCategory(goods);
			}

			return goodsList;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 添加商品
	 */
	public void addGoods(Goods goods) {
		try {
			String sql = "INSERT INTO `t_goods`(`gid`,`gname`,`price`,`image`,`cid`,`parameter`,`desc`,`gdel`,`promote`) VALUES (?,?,?,?,?,?,?,?,?)";
			Object[] params = { goods.getGid(), goods.getGname(),
					goods.getPrice(), goods.getImage(), goods.getCid(),
					goods.getParameter(), goods.getDesc(), goods.getGdel(),
					goods.getPromote() };
			queryRunner.update(sql, params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 修改商品
	 */
	public void editGoods(Goods goods) {
		try {
			StringBuffer sqlBuf = new StringBuffer();
			List<Object> params = new ArrayList<Object>();

			sqlBuf.append("UPDATE `t_goods` SET `gid`=`gid`");
			if (goods.getGname() != null
					&& goods.getGname().trim().length() > 0) {
				sqlBuf.append(",`gname`=?");
				params.add(goods.getGname());
			}
			if (goods.getPrice() != null) {
				sqlBuf.append(",`price`=?");
				params.add(goods.getPrice());
			}
			if (goods.getImage() != null
					&& goods.getImage().trim().length() > 0) {
				sqlBuf.append(",`image`=?");
				params.add(goods.getImage());
			}
			if (goods.getCid() != null && goods.getCid().trim().length() > 0) {
				sqlBuf.append(",`cid`=?");
				params.add(goods.getCid());
			}
			if (goods.getParameter() != null
					&& goods.getParameter().trim().length() > 0) {
				sqlBuf.append(",`parameter`=?");
				params.add(goods.getParameter());
			}
			if (goods.getDesc() != null && goods.getDesc().trim().length() > 0) {
				sqlBuf.append(",`desc`=?");
				params.add(goods.getDesc());
			}
			if (goods.getPromote() != null
					&& goods.getPromote().trim().length() > 0) {
				sqlBuf.append(",`promote`=?");
				params.add(goods.getPromote());
			}
			sqlBuf.append(" WHERE gid=?");
			params.add(goods.getGid());

			queryRunner.update(sqlBuf.toString(), params.toArray());
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 商品销量排序
	 */
	public List<SortGoods> sellingOrderSortGoods() {
		try {
			String sql = "SELECT SUM(`count`) AS `count`,SUM(`subtotal`) AS `total`,gid FROM `t_orderitem` GROUP BY gid ORDER BY `count` DESC,`total` DESC";

			List<SortGoods> sortGoodsList = queryRunner.query(sql,
					new BeanListHandler<SortGoods>(SortGoods.class));

			if (sortGoodsList != null) {
				for (SortGoods sortGoods : sortGoodsList) {
					sortGoods.setGoods(findById(sortGoods.getGid()));
				}
			}

			return sortGoodsList;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 按分类查询商品
	 */
	public List<Goods> findGoodsByCid(String cid) {
		try {
			String sql = "SELECT * FROM `t_goods` WHERE `gdel`='0' AND `cid`=?";
			return queryRunner.query(sql, new BeanListHandler<Goods>(
					Goods.class), cid);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 查询宣传商品
	 */
	public List<Goods> findGoodsByPromote(String promote, int limit) {
		try {
			String sql = "SELECT * FROM `t_goods` WHERE `gdel`='0' AND `promote`=? LIMIT 0,"
					+ limit;
			Object[] params = { promote, limit };
			return queryRunner.query(sql, new BeanListHandler<Goods>(
					Goods.class), promote);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

}
